package com.edu.modules.baseinfo.dao;

import com.edu.modules.baseinfo.model.Lesson;
import com.edu.modules.baseinfo.model.LessonDetail;
import com.edu.modules.baseinfo.model.LessonRecord;
import com.sari.common.persistence.BaseDao;
import com.sari.common.utils.DateUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * Created by lab on 2016/7/15.
 */
@Repository
public class LessonDao extends BaseDao {

	public List<Lesson> getLessonList(String lessonName,String lessonClass,String nowDate){

		String sql = "select lesson_id,lesson_name,lesson_class,lesson_content,lesson_detail_url," +
				"start_date,price,week,sign_number from t_lesson WHERE sign_end_time > ?";
		if(!lessonName.equals("")){
			sql += " and lesson_name like '%"+lessonName+"%'";
		}
		if(!lessonClass.equals("")){
			sql += " and lesson_class like '%"+lessonClass+"%'";
		}

		return findList(sql,Lesson.class,nowDate);
	}

	public LessonDetail getLessonDetail(String lessonId){

		String sql = "select lesson_id,lesson_name,lesson_class,lesson_content,lesson_store_id,week," +
				"start_date,end_date,lesson_start_time,lesson_end_time,price,max_number," +
				"sign_number,sign_end_time from t_lesson WHERE lesson_id = ?";

		return (LessonDetail) findForObject(sql,LessonDetail.class,lessonId);
	}

	public int updateSignNumber(String lessonId){

		String sql = "UPDATE t_lesson SET sign_number = sign_number+1 WHERE lesson_id = ?";

		return saveOrUpdateOrDelete(sql,lessonId);
	}

	public List getTeacherName(){

		String sql = "select distinct teacher_name from t_lesson";

		return super.listBySql(sql);
	}

	public List getLessonGroup(){

		String sql = "SELECT lesson_name FROM t_lesson GROUP BY lesson_name";

		return super.listBySql(sql);
	}

	public List getTeacherLessonGroup(String teacherId){

		String sql = "SELECT subject_type FROM t_teacher WHERE teacher_id = ? GROUP BY subject_type";

		return super.listBySql(sql, teacherId);
	}

	public List getTeacherClass(String teacherId){

		String sql = "SELECT b.lesson_class FROM t_teacher a,t_lesson b WHERE a.teacher_name = " +
				"b.teacher_name AND a.subject_type = b.lesson_name AND a.teacher_id = ?";

		return super.listBySql(sql, teacherId);
	}

	public void saveWeeks(String lessonId,String weeks[],String createTime){

		String sql = "insert into t_lesson_week (lesson_id,weeks,create_time) values ";
		for (int i=0;i<weeks.length;i++){
			sql+="('"+lessonId+"','"+weeks[i]+"','"+createTime+"'),";
		}

		super.getJdbcTemplate().execute(sql.substring(0,sql.length()-1));
	}

	public List getLessonVideoList(String studentId){

		String sql = "select b.lesson_record_id,d.lesson_id,b.lesson_name,b.lesson_class,b.teacher_name," +
				"b.subject,b.video_url,b.picture_url,b.date_time from t_sign_up a,t_lesson_record b,t_lesson d where " +
				"a.lesson_id = d.lesson_id and b.lesson_name = d.lesson_name and b.lesson_class = d.lesson_class and" +
				" b.teacher_name = d.teacher_name and a.is_paid = 1 and a.student_id = ? ORDER BY b.date_time DESC";

		return listBySql(sql,studentId);
	}

	public List getMyLessonList(String studentId){

		String sql = "select b.lesson_id,b.lesson_name,b.lesson_class,b.lesson_content,b.start_date, " +
				"end_date,lesson_start_time,lesson_end_time from t_sign_up a, t_lesson b where a.lesson_id = b.lesson_id and a.is_paid = 1 and a.student_id = ?";

		return listBySql(sql,studentId);
	}

	public List getLessonByPage(String lessonName,String lessonClass,
								String startTime,String endTime,int offset,int limit){

		String sql = "select * from t_lesson where 1=1";

		if(!lessonName.equals("")){
			sql += " and lesson_name like '%"+lessonName+"%'";
		}
		if(!lessonClass.equals("")){
			sql += " and lesson_class like '%"+lessonClass+"%'";
		}
		if((!startTime.equals(""))&&(!endTime.equals(""))){
			sql += " and start_date between '"+startTime+"' and '"+endTime+"'";
		}
		sql +=" ORDER BY create_time DESC limit ?,?";

		return super.listBySql(sql,offset,limit);
	}

	public List getLessonWeeks(String lessonId){

		String sql = "select weeks from t_lesson_week where lesson_id = ?";

		return super.listBySql(sql,lessonId);
	}

	public void updateLessonWeeks(String weeks[],String lessonId){

		String sql = "delete from t_lesson_week where lesson_id = ?";

		super.saveOrUpdateOrDelete(sql,lessonId);

		saveWeeks(lessonId,weeks, DateUtils.getDateTime());
	}

	public int getLessonCounts(String lessonName,String lessonClass,
							   String startTime,String endTime){

		String sql = "select count(*) from t_lesson where 1=1";
		if(!lessonName.equals("")){
			sql += " and lesson_name like '%"+lessonName+"%'";
		}
		if(!lessonClass.equals("")){
			sql += " and lesson_class like '%"+lessonClass+"%'";
		}
		if((!startTime.equals(""))&&(!endTime.equals(""))){
			sql += " and start_date between '"+startTime+"' and '"+endTime+"'";
		}

		return super.getCount(sql);
	}

	public String getLessonId(String teacherId,String lessonName,String lessonClass){

		String sql = "select b.lesson_id from t_teacher a,t_lesson b where b.lesson_name = ? and b.lesson_class = ? " +
				     "and a.teacher_name = b.teacher_name and a.teacher_id = ?";

		return getJdbcTemplate().queryForObject(sql, String.class,lessonName, lessonClass,teacherId);
	}

	public List<Map<String, Object>> getLesson(){

		String sql = "select lesson_id,lesson_name,lesson_class,lesson_start_time from t_lesson";

		return super.listBySql(sql);
	}

	public List getLessonCalendar(String studentId,String dateTime){

		String sql = "select b.week,b.start_date,b.end_date from t_sign_up a,t_lesson b where" +
				" a.lesson_id = b.lesson_id and b.end_date >= ? and a.is_paid = 1 and a.student_id = ?";

		return super.listBySql(sql,dateTime,studentId);
	}

	public List getMyLesson(String studentId,String dateTime,String week){

		String sql = "select b.lesson_name,b.lesson_class,b.lesson_start_time,b.lesson_end_time,b.lesson_content from t_sign_up a,t_lesson b " +
				"where a.lesson_id = b.lesson_id and b.week like '%"+week+"%' and a.is_paid = 1 and a.student_id = ? and b.end_date >= ?";

		return super.listBySql(sql,studentId,dateTime);
	}
}
